﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

/// <summary>
/// SolicitudBL maneja las solicutudes de usuarios y empleados
/// </summary>
public class SolicitudBL
{
    /*************GuardarSolicitud***********/
    /**Este método recibe como parámetro un objeto de tipo solicitud*/
    /**y almacena este mismo objeto dentro de la tabla "solicitudes**/
    public void GuardarSolicitud(Solicitud solicitud)
    {
        try
        {
            //Query que se ejecutará en la base de datos
            string query = "INSERT INTO `solicitudes` (tipo, enviada_por_id, recibida_por_id, motivo, estado, fecha_envio, fecha_aceptada, departamento, urgencia, encargado_id) "+
            "VALUES ('"+solicitud.Tipo+"'," + solicitud.EnviadaPorId + "," + solicitud.RecibidaPorId + ",'" + solicitud.Motivo + "'," + solicitud.Estado + ", NOW(), NOW(),'"+solicitud.Departamento+"',"+solicitud.Urgencia+","+-1+")";

            //Conexion a MySQL
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //Comando de MySQL
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execucion del comando
            cmd.ExecuteNonQuery();
            //Se cierra la conexión
            myConnection.Close();

        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public string ModificarSolicitud(long solicitudId, int accion)
    {
        try
        {
            // RequireRole(sessionUUID, Role.WRITE_ACCESS, Role.USERS);
            string query = "UPDATE `solicitudes` set estado = "+accion +" WHERE solicitudes_id = " + solicitudId;
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            myConnection.Close();

            return "ok";
        }
        catch (Exception ex)
        {

            //    _Logger.Error("Error in DeleteCourse()", ex);

            return "error";
        }
    }

    public void BorrarSolicitud(long solicitudId, string motivo)
    {
        try
        {
            // RequireRole(sessionUUID, Role.WRITE_ACCESS, Role.USERS);
            string query = "UPDATE `solicitudes` set estado = 3, motivo = '"+motivo+"' WHERE solicitudes_id = " + solicitudId;
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            myConnection.Close();
        }
        catch (Exception ex)
        {

            //    _Logger.Error("Error in DeleteCourse()", ex);

            throw ex;
        }
    }

    public List<string> ObtenerSolicitudesUsuario(string usuarioId)
    {
        List<string> solicitudes = new List<string>();

        MySqlConnection myConnection = new MySqlConnection();
        myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection.Open();
        var query = String.Format("SELECT solicitudes_id, tipo, motivo, fecha_envio, estado, departamento FROM solicitudes WHERE enviada_por_id={0}", usuarioId);
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, myConnection);
        MySqlDataReader dataReader = cmd.ExecuteReader();

        while (dataReader.Read())
        {
            var numsolicitud = dataReader["solicitudes_id"];
            var solicitud = dataReader["tipo"];
            var motivo = dataReader["motivo"];
            var fecha = dataReader["fecha_envio"];
            var estatus = "";
            

            switch ((int)dataReader["estado"])
            {
                case 1: estatus = "Pendiente"; break;
                case 2: estatus = "Trabajo en proceso"; break;
                case 3: estatus = "Cerrada"; break;
                case 4: estatus = "Rechazada"; break;
                case 5: estatus = "Aceptada"; break;
            }

            var departamento = dataReader["departamento"];

            string texto = string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td>", numsolicitud, solicitud, motivo, fecha, estatus, departamento);
            if ((int)dataReader["estado"] != 3 && (int)dataReader["estado"] != 4)
                texto += "<td><input type = \"button\" value = \"Cancelar\" OnClick = \"cancelar("+numsolicitud+")\"></td></tr>";
            solicitudes.Add(texto);
        }

        dataReader.Close();

        //close Connection
        myConnection.Close();

        return solicitudes;
    }

    public List<string> ObtenerSolicitudes(string usuarioId)
    {
        //en el string idusuario se debe de poner el id del jefe (es el usuario que está logeado).
        List<string> solicitudes = new List<string>();
        MySqlConnection myConnection = new MySqlConnection();
        myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection.Open();
        var query = String.Format("SELECT solicitudes_id, tipo, motivo, urgencia, enviada_por_id FROM solicitudes WHERE encargado_id='{0}' AND estado < 4", usuarioId);
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, myConnection);
        MySqlDataReader dataReader = cmd.ExecuteReader();
       // int cont = 1;
        //Read the data and store them in the list
        var urgencia = "";
        while (dataReader.Read())
        {
            var numsolicitud = dataReader["solicitudes_id"];
            var solicitud = dataReader["tipo"];
            var estatus = dataReader["motivo"];
            var urgencia1 = dataReader["urgencia"];

            if (urgencia1 == "0")
            {
                urgencia = "Leve";
            }else
            {
                urgencia = "Urgente";
            }

            query = "Select nombre from usuarios WHERE usuarios_id = " + dataReader["enviada_por_id"];
            string nombre = "";
            MySqlConnection myConnection1 = new MySqlConnection();
            myConnection1.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection1.Open();

            MySqlCommand cmd1 = new MySqlCommand(query, myConnection1);
            MySqlDataReader dataReader1 = cmd1.ExecuteReader();
            
            var texto = "";
            while (dataReader1.Read())
            {
                nombre = (string)dataReader1["nombre"];
                texto = string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td>", numsolicitud, solicitud, estatus, nombre, urgencia);
                texto += "<td><select id = \"select"+numsolicitud+"\">";
                texto += "<option value=\"2\">Trabajo en proceso</option>";
                texto += "<option value=\"3\">Cerrada</option>";
                texto += "<option value=\"4\">Rechazada</option>";
                texto += "<option value=\"5\">Aceptada</option></select></td>";
                texto += "<td><input type='button' value ='Aceptar' id='aceptar'"+ numsolicitud + "' onClick='asignar(" + numsolicitud + ")'></td> </tr>";
//                texto += "<td><input type='button' value = \"Rechazar\" id = \"rechazar" + numsolicitud + "\" runat = \"server\" OnClick=\"rechazar(" + numsolicitud + ")\"></td>";
            }
           

            solicitudes.Add(texto);
          //  cont++;

            dataReader1.Close();
            myConnection1.Close();
        }
        

        //close Data Reader
        dataReader.Close();
        
        //close Connection
        myConnection.Close();

        return solicitudes;

    }

    public List<string> ObtenerEmpleados(string usuarioId)
    {
        MySqlConnection myConnection2 = new MySqlConnection();
        List<string> empleados = new List<string>();
        myConnection2.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection2.Open();
        var query = String.Format("SELECT usuarios_id, nombre FROM usuarios WHERE jefe_id='{0}'", usuarioId);
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd2 = new MySqlCommand(query, myConnection2);
        MySqlDataReader dataReader2 = cmd2.ExecuteReader();

        while(dataReader2.Read())
        {
            empleados.Add(dataReader2["usuarios_id"] + "," + dataReader2["nombre"]);
        }

        //close Data Reader
        dataReader2.Close();

        //close Connection
        myConnection2.Close();


        string emplea = "";

        foreach (string empleado in empleados)
        {
            string[] emple = empleado.Split(',');
            emplea += "<option value = \""+emple[0]+"\">"+emple[1]+"</option>";
        }

        //en el string idusuario se debe de poner el id del jefe (es el usuario que está logeado).
        List<string> solicitudes = new List<string>();
        MySqlConnection myConnection = new MySqlConnection();
        myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection.Open();
        query = String.Format("SELECT solicitudes_id, tipo, motivo, urgencia, enviada_por_id FROM solicitudes WHERE recibida_por_id='{0}' AND estado < 3 AND encargado_id < 0", usuarioId);
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, myConnection);
        MySqlDataReader dataReader = cmd.ExecuteReader();
        // int cont = 1;
        //Read the data and store them in the list
        var urgencia = "";
        while (dataReader.Read())
        {
            var numsolicitud = dataReader["solicitudes_id"];
            var solicitud = dataReader["tipo"];
            var estatus = dataReader["motivo"];
            var urgencia1 = dataReader["urgencia"];

            if (urgencia1 == "0")
            {
                urgencia = "Leve";
            }
            else
            {
                urgencia = "Urgente";
            }

            query = "Select nombre from usuarios WHERE usuarios_id = " + dataReader["enviada_por_id"];
            string nombre = "";
            MySqlConnection myConnection1 = new MySqlConnection();
            myConnection1.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection1.Open();

            MySqlCommand cmd1 = new MySqlCommand(query, myConnection1);
            MySqlDataReader dataReader1 = cmd1.ExecuteReader();

            var texto = "";
            while (dataReader1.Read())
            {



                nombre = (string)dataReader1["nombre"];
                texto = string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td>", numsolicitud, solicitud, estatus, nombre, urgencia);
                texto += "<td><select id = \"select" + numsolicitud + "\">";
                texto += emplea;
                texto += "</select>";
                texto += "<td><input type=\"button\" value =\"Asignar\" id=\"asignar" + numsolicitud + "\"OnClick=\"asignar(" + numsolicitud + ")\"></td> </tr>";
                //                texto += "<td><input type='button' value = \"Rechazar\" id = \"rechazar" + numsolicitud + "\" runat = \"server\" OnClick=\"rechazar(" + numsolicitud + ")\"></td>";
            }


            solicitudes.Add(texto);
            //  cont++;

            dataReader1.Close();
            myConnection1.Close();
        }


        //close Data Reader
        dataReader.Close();

        //close Connection
        myConnection.Close();

        return solicitudes;

    }

    public string AsignarEncargado(long encargadoId, long solicitudId)
    {

        try
        {
            // RequireRole(sessionUUID, Role.WRITE_ACCESS, Role.USERS);
            string query = "UPDATE `solicitudes` set encargado_id = " + encargadoId + " WHERE solicitudes_id = " + solicitudId;
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            myConnection.Close();

            return "ok";
        }
        catch (Exception ex)
        {

            //    _Logger.Error("Error in DeleteCourse()", ex);

            return "error";
        }

    }


    public Solicitud ObtenerSolicitud(long solicitudId)
    {
        List<string> solicitudes = new List<string>();
        Solicitud solicitud = new Solicitud();


        MySqlConnection myConnection = new MySqlConnection();
        myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection.Open();
        var query = String.Format("SELECT * FROM solicitudes WHERE solicitudes_id={0}", solicitudId);
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, myConnection);
        MySqlDataReader dataReader = cmd.ExecuteReader();

        while (dataReader.Read())
        {
            var numsolicitud = dataReader["solicitudes_id"];
            var tipo = dataReader["tipo"];
            var enviadaPorId = dataReader["enviada_por_id"];
            var recibidaPorId = dataReader["recibida_por_id"];
            var motivo = dataReader["motivo"];
            var estado = dataReader["estado"];
            var fecha = dataReader["fecha_envio"];
            var departamento = dataReader["departamento"];
            var urgencia = dataReader["urgencia"];

            solicitud.Departamento = (string)departamento;
            solicitud.EnviadaPorId = (int)enviadaPorId;
            solicitud.Estado =(int) estado;
            solicitud.FechaAceptada = (DateTime)fecha;
            solicitud.FechaEnvio = (DateTime)fecha;
            solicitud.Motivo = (string)motivo;
            solicitud.RecibidaPorId = (int)recibidaPorId;
            solicitud.Tipo = (string)tipo;
            solicitud.Urgencia = (int)urgencia;

        }

        dataReader.Close();

        //close Connection
        myConnection.Close();

        return solicitud;
    }



}